﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.SqlClient;

namespace syc.Store
{
    public partial class 产品收发存统计 : System.Web.UI.Page
    {
        protected bool report = false;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.Date1.Text = DateTime.Today.ToLongDateString();
            }

        }
        protected string GetKunHao(int kh)
        {
            string tmp = kh.ToString();
            tmp = "000000000".Substring(tmp.Length) + tmp;
            tmp = tmp.Substring(tmp.Length-9);
            return tmp.Substring(0,4)+"-"+tmp.Substring(tmp.Length-4);
        }

        protected string GetState()
        {
            string state = "";

            if (this.ChanPin.Text != "0")
            {
                state += " and 产品类别ID =" + this.ChanPin.Text;
            }

            if (this.FenChang.Text != "0")
            {
                if (this.FenChang.Text == "2")
                {
                    state += " and (生产线=201 or 生产线=202)";
                }
                else if (this.FenChang.Text == "5")
                {
                    state += " and (生产线=501 or 生产线=502)";
                }
                else
                {
                    state += " and 生产线=" + this.FenChang.Text;
                }
            }
            if (this.GongHuo.Text != "0")
            {
                state += " and 原料供货ID=" + this.GongHuo.Text;
            }
            
            DateTime d;
            if (DateTime.TryParse(this.Date1.Text, out d))
            {
                state += " and 入库日期>='" + d.ToShortDateString() + "'";
            }
            if (DateTime.TryParse(this.Date2.Text, out d))
            {
                state += " and 入库日期<='" + d.ToShortDateString() + "'";
            }

            if (state.StartsWith(" and"))
            {
                state = state.Substring(4);
            }
            if (state != "")
            {
                state = " where " + state;
            }
            return state;
        }

        protected string GetState2()
        {
            string state = "";

            if (this.ChanPin.Text != "0")
            {
                state += " and 产品类别ID =" + this.ChanPin.Text;
            }

            if (this.FenChang.Text != "0")
            {
                if (this.FenChang.Text == "2")
                {
                    state += " and (生产线=201 or 生产线=202)";
                }
                else if (this.FenChang.Text == "5")
                {
                    state += " and (生产线=501 or 生产线=502)";
                }
                else
                {
                    state += " and 生产线=" + this.FenChang.Text;
                }
            }
            if (this.GongHuo.Text != "0")
            {
                state += " and 订货单位ID=" + this.GongHuo.Text;
            }

            DateTime d;
            if (DateTime.TryParse(this.Date1.Text, out d))
            {
                state += " and 日期>='" + d.ToShortDateString() + "'";
            }
            if (DateTime.TryParse(this.Date2.Text, out d))
            {
                state += " and 日期<='" + d.ToShortDateString() + "'";
            }

            if (state.StartsWith(" and"))
            {
                state = state.Substring(4);
            }
            if (state != "")
            {
                state = " where " + state;
            }
            return state;
        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {
            string con1 = "select count(id) as 入库捆,sum(重量) as 入库重,sum(长度) as 入库长,sum(支数) as 入库支 from 产品库存 where 状态>0";
            string con2 = "select count(id) as 库存捆,sum(重量) as 库存重,sum(长度) as 库存长,sum(支数) as 库存支 from 产品库存 where 状态=1";
            string con3 = "select count(id) as 出库捆,sum(重量) as 出库重,sum(长度) as 出库长,sum(支数) as 出库支 from 产品库存 where 状态=2";
            int chanpin = 0;
            int fenchang = 0;
            int gonghuo = 0;

            DateTime d1, d2;
            int.TryParse(this.ChanPin.Text, out chanpin);
            int.TryParse(this.FenChang.Text, out fenchang);
            int.TryParse(this.GongHuo.Text, out gonghuo);
            if (chanpin != 0)
            {
                con1 += " and 产品类别ID=" + chanpin;
                con2 += " and 产品类别ID=" + chanpin;
                con3 += " and 产品类别ID=" + chanpin;

            }
            if (fenchang != 0)
            {
                if (fenchang == 2)
                {
                    con1 += " and 生产线 between 200 and 299";
                    con2 += " and 生产线 between 200 and 299";
                    con3 += " and 生产线 between 200 and 299";
                }
                else if (fenchang == 5)
                {
                    con1 += " and 生产线 between 500 and 599";
                    con2 += " and 生产线 between 500 and 599";
                    con3 += " and 生产线 between 500 and 599";
                }
                else
                {
                    con1 += " and 生产线=" + fenchang;
                    con2 += " and 生产线=" + fenchang;
                    con3 += " and 生产线=" + fenchang;
                }
            }
            if (gonghuo != 0)
            {
                con1 += " and 原料供货ID=" + gonghuo;
                con2 += " and 原料供货ID=" + gonghuo;
                con3 += " and 原料供货ID=" + gonghuo;

            }

            if (DateTime.TryParse(Date1.Text, out d1))
            {
                con1 += " and 入库日期>='" + d1.ToShortDateString() + "'";
                con2 += " and 入库日期>='" + d1.ToShortDateString() + "'";
                con3 += " and 出库日期>='" + d1.ToShortDateString() + "'";

            }
            else if (Date1.Text != "")
            {
                this.Label_Result.Text = "起始日期输入有误";
                return;
            }
            if (DateTime.TryParse(Date2.Text, out d2))
            {
                con1 += " and 入库日期<='" + d2.ToShortDateString() + "'";

                con2 = con2.Replace("状态=1", "(状态=1 or 出库日期>'" + d2.ToShortDateString() + "')");

                con2 += " and 入库日期<='" + d2.ToShortDateString() + "'";

                con3 += " and 出库日期<='" + d2.ToShortDateString() + "'";

            }
            else if (Date2.Text != "")
            {
                this.Label_Result.Text = "截止日期输入有误";
                return;
            }

            string sql = "select * from ("+con1+") t1,("+con2+") t2,("+con3+") t3";

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = sql;
            Cmd.CommandType = CommandType.Text;

            SqlDataReader Reader = Cmd.ExecuteReader();

            if (Reader.Read())
            {
                this.Label_Result.Text = "";
                if (!Reader.IsDBNull(1))
                {
                    this.Label_Result.Text = "<span style='display:inline-block;width:200px;'>入库捆数：" + (int)Reader[0]
                        + " 捆</span><span style='display:inline-block;width:200px;'>总支数：" + (int)Reader[3]
                        + " 支</span><span style='display:inline-block;width:200px;'>总重量：" + (decimal)Reader[1]
                        + " kg</span><span style='display:inline-block;width:200px;'>总长度：" + (decimal)Reader[2] 
                        + " m</span><br>";
                }
                if (!Reader.IsDBNull(10))
                {
                    this.Label_Result.Text += "<span style='display:inline-block;width:200px;'>出库捆数：" + (int)Reader[8]
                        + " 捆</span><span style='display:inline-block;width:200px;'>总支数：" + (int)Reader[11]
                        + " 支</span><span style='display:inline-block;width:200px;'>总重量：" + (decimal)Reader[9]
                        + " kg</span><span style='display:inline-block;width:200px;'>总长度：" + (decimal)Reader[10] 
                        + " m</span><br>";
                }
                if (!Reader.IsDBNull(5))
                {
                    this.Label_Result.Text += "<span style='display:inline-block;width:200px;'>库存捆数：" + (int)Reader[4]
                        + " 捆</span><span style='display:inline-block;width:200px;'>总支数：" + (int)Reader[7]
                        + " 支</span><span style='display:inline-block;width:200px;'>总重量：" + (decimal)Reader[5]
                        + " kg</span><span style='display:inline-block;width:200px;'>总长度：" + (decimal)Reader[6] 
                        + " m</span><br>";
                }
       
            }

            Reader.Close();
            Reader.Dispose();

            Cmd.Dispose();
            Conn.Close();
            Conn.Dispose();
            report = true;
        }

        protected void Button_Reset_Click(object sender, EventArgs e)
        {
            this.ChanPin.Text = "0";
            this.FenChang.Text = "0";
            this.GongHuo.Text = "0";
            this.Date1.Text = "";
            this.Date2.Text = "";
        }


    }
}
